package test;

import file.MyTime;
import house.House;
import house.PreHouse;

import java.util.ArrayList;
import java.util.Map.Entry;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.regex.PatternSyntaxException;

import database.DBconnection;
//对预处理的表 小区再进行下处理

public class PreDeal3 {
	private static DBconnection dBconnection=DBconnection.getDbConnection();
	
	private static ArrayList<House> houseList=new ArrayList<House>();
	
	private static ArrayList<PreHouse> prehouseList=new ArrayList<PreHouse>(); 
	
	public static ArrayList<PreHouse> getPreHouse(String tableName){
		houseList=RecordToHouse.recordToHouse(tableName);
		for(House house:houseList){
			PreHouse preHouse=new PreHouse();
			preHouse.pay=Integer.parseInt(house.pay);
			//小区处理
			preHouse.community=PreDeal3.stringFilter(house.community).replace("查看周边高薪工作位置", "").trim();
			preHouse.community=preHouse.community.replace("2室2厅精装个人房", "").replace("合租", "").trim();
			preHouse.community=preHouse.community.replace("个人", "").replace("三房出租", "").replace("靠近晨曦园", "").trim();
			preHouse.community=preHouse.community.replace("精装三室一", "").replace("房源无中", "").replace("无中介费", "").trim();
			preHouse.community=preHouse.community.replace("精装房出租", "").replace("三房豪装出租", "").replace("房源", "").trim();
			preHouse.community=preHouse.community.replace("单身公寓", "").replace("出租", "").replace("精装修3房", "").trim();
			preHouse.community=preHouse.community.replace("小单间350月", "").replace("两室两厅 精", "").replace("两室两厅精", "").trim();
			preHouse.community=preHouse.community.replace("朝南车库装修", "").replace("两室一厅设全", "").replace("居家朝南主卧", "").trim();
			preHouse.community=preHouse.community.replace("精装修公寓", "").replace("精装单身公寓", "").replace("精装朝南公寓", "").trim();
			preHouse.community=preHouse.community.replace("三房精装", "").replace("精装拎包入住", "").replace("精装朝南公寓", "").trim();
			preHouse.community=preHouse.community.replace("希望干净的夫妻住", "").replace("三室两厅精装", "").replace("2房2000元", "").trim();
			preHouse.community=preHouse.community.replace("豪装3房2300元", "").replace("2室1厅", "").replace("3室2厅2卫精装", "").trim();
			preHouse.community=preHouse.community.replace("复试单身公寓出租", "").replace("精装三室两厅两卫", "").replace("一室一厅一厨一卫出租", "").trim();
			preHouse.community=preHouse.community.replace("精装两室一厅", "").replace("店面车库转让", "").replace("大户出租", "").trim();
			preHouse.community=preHouse.community.replace("三房精装首租", "").replace("自盖住宅好房", "").replace("三房豪装", "").trim();
			preHouse.community=preHouse.community.replace("精装修", "").replace("个人房屋低价出租", "").replace("2室带院子", "").trim();
			preHouse.community=preHouse.community.replace("朝南", "").replace("主卧", "").replace("次卧", "").trim();
			preHouse.community=preHouse.community.replace("3室1厅1卫", "").replace("诚心", "").replace("非中介", "").trim();
			preHouse.community=preHouse.community.replace("整租2600", "").replace("精装", "").replace("主室", "").trim();
			preHouse.community=preHouse.community.replace("2500一月设全", "").replace("15", "").replace("具体电话联系", "").trim();
			preHouse.community=preHouse.community.replace("独立小区环境好", "").replace("有房", "").replace("了", "").trim();
			preHouse.community=preHouse.community.replace("有", "").replace("单间", "").replace("免中介费", "").trim();
			preHouse.community=preHouse.community.replace("独立卫生间", "").replace("三房一厅一卫2100", "").replace("免中介费", "").trim();
			preHouse.community=preHouse.community.replace(" ", "").trim();
			
			preHouse.area=house.area.replace("㎡", "").replace("平米", "").trim();
			int index=preHouse.area.indexOf(".");
			if(index>0){
				preHouse.area=preHouse.area.substring(0, index).trim();
			}
			
			preHouse.id=house.id;
			preHouse.title=house.title.trim();
			preHouse.crawlTime=house.crawlTime.replaceAll("-", "").replace("年", "").replace("月", "").replace("日", "").trim();
			preHouse.time=house.time.trim();
			preHouse.source=house.source.trim();

			preHouse.payStyle=house.payStyle.trim();
			preHouse.houseStyle=house.houseStyle.trim();
			preHouse.rentStyle=house.rentStyle.trim();
			preHouse.style=house.style.trim();

			preHouse.decoration=house.decoration.trim();
			preHouse.direction=house.direction.trim();
			preHouse.floor=house.floor.replace("层", "").trim();
			
			preHouse.location=house.location.replace("苏州-", "").trim().replace(" ", "-");
			preHouse.installation=house.installation.trim();
			preHouse.contactMan=house.contactMan.trim();
			preHouse.contactWay=house.contactWay.trim();
			preHouse.houseDetails=PreDeal3.stringFilter2(house.houseDetails.trim());
			preHouse.url=house.url.trim();
			preHouse.picNum=house.picNum;
			
			prehouseList.add(preHouse);
		}
		return prehouseList;
	}
	//存入数据库,pre为表前缀
	public static void storePreHouse(String tableName){
		int count=0,total=prehouseList.size(),i;
		for(PreHouse preHouse:prehouseList){
			String sql="insert IGNORE into"+" "+"pre_"+tableName+"(id,来源,标题,抓取时间,发布时间,租金,押付方式,户型,租凭方式,面积,房屋类型,装修,朝向,楼层,小区,位置,配置,联系人,联系方式,房源描述,图片数,URL)"
					+" "+"values("+preHouse.id+","+"'"+preHouse.source+"'"+","+"'"+preHouse.title+"'"+","+"'"+preHouse.crawlTime+"'"+","
					+"'"+preHouse.time+"'"+","+preHouse.pay+","+"'"+preHouse.payStyle+"'"+","+"'"+preHouse.houseStyle+"'"+","
					+"'"+preHouse.rentStyle+"'"+","+"'"+preHouse.area+"'"+","+"'"+preHouse.style+"'"+","+"'"+preHouse.decoration+"'"+","
					+"'"+preHouse.direction+"'"+","+"'"+preHouse.floor+"'"+","+"'"+preHouse.community+"'"+","
					+"'"+preHouse.location+"'"+","+"'"+preHouse.installation+"'"+","+"'"+preHouse.contactMan+"'"+","+"'"+preHouse.contactWay+"'"+","
					+"'"+preHouse.houseDetails+"'"+","+preHouse.picNum+","+"'"+preHouse.url+"'"+")";
//			System.out.println(sql);
			i=dBconnection.executeUpdate(sql);
			if(i>0){
				count++;
//				System.out.println(i+"条数据插入成功！");
			}
			else {
//				System.out.println("房源数据插入失败!");
//				System.out.println(sql);
			}
			
		}
		System.out.println("需预处理："+total);
		System.out.println("预处理了："+count);
		System.out.println("预处理表中已有："+(total-count));
	}
	
	//过滤特殊字符
	public static String stringFilter(String str) throws PatternSyntaxException{
		String regEx="[`~!@#$%^&*()+=|{}':;',\\[\\].<>/?~！@#￥%……&*（）——+|{}【】‘；：”“’。，、？·@&?]／?()";
//		String regEx="[^u4e00-u9fa5]";		
		Pattern pattern=Pattern.compile(regEx);
		Matcher matcher=pattern.matcher(str);
		return matcher.replaceAll("").trim();
	}
	
	//过滤奇异字符
	public static String stringFilter2(String str) throws PatternSyntaxException{
		String regEx="'?";
//		String regEx="[^u4e00-u9fa5]";		
		Pattern pattern=Pattern.compile(regEx);
		Matcher matcher=pattern.matcher(str);
		return matcher.replaceAll("").trim();
	}
	
	
	public static void clear() {
		houseList.clear();
		prehouseList.clear();
	}
	
	public static void main(String[] args) {
		// TODO Auto-generated method stub
		long start=System.currentTimeMillis();
		String[] tableName={"pre_ganji_copy","pre_tongcheng_copy","pre_anjuke_copy"};
		for(int i=0;i<3;i++){
			PreDeal3.clear();
			System.out.println("houseList:"+PreDeal3.houseList);
			System.out.println("prehouseList:"+PreDeal3.prehouseList);
			System.out.println("预处理:"+tableName[i]);
			PreDeal3.getPreHouse(tableName[i]);
			PreDeal3.storePreHouse(tableName[i]);
		}
//		String string="3564456456?(查看周边高薪工作)位置";
////		String str=stringFilter2(string);
//		String str2=PreDeal3.stringFilter(string).replace("查看周边高薪工作位置", "").trim();
//		System.out.println(str2);
	}

}
